﻿using System.Data;
using System.Data.SQLite;

namespace h1.Data
{
    internal class DatabaseHelper
    {
        private const string ConnectionString = "Data Source=school.db";

        public static void InitializeDatabase()
        {
            using var conn = new SQLiteConnection(ConnectionString);
            conn.Open();

            string[] commands =
            {
            "CREATE TABLE IF NOT EXISTS School (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT)",
            "CREATE TABLE IF NOT EXISTS Classroom (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, SchoolId INTEGER, FOREIGN KEY(SchoolId) REFERENCES School(Id))",
            "CREATE TABLE IF NOT EXISTS Student (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, ClassroomId INTEGER, FOREIGN KEY(ClassroomId) REFERENCES Classroom(Id))",
            "CREATE TABLE IF NOT EXISTS Log (Id INTEGER PRIMARY KEY AUTOINCREMENT, Operation TEXT, Timestamp TEXT)"
        };

            foreach (var cmdText in commands)
            {
                using var cmd = new SQLiteCommand(cmdText, conn);
                cmd.ExecuteNonQuery();
            }
        }

        public static void ExecuteNonQuery(string sql, params SQLiteParameter[] parameters)
        {
            using var conn = new SQLiteConnection(ConnectionString);
            conn.Open();
            using var cmd = new SQLiteCommand(sql, conn);
            cmd.Parameters.AddRange(parameters);
            cmd.ExecuteNonQuery();
            LogOperation(sql);
        }

        public static DataTable ExecuteQuery(string sql, params SQLiteParameter[] parameters)
        {
            using var conn = new SQLiteConnection(ConnectionString);
            conn.Open();
            using var cmd = new SQLiteCommand(sql, conn);
            cmd.Parameters.AddRange(parameters);
            using var adapter = new SQLiteDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        private static void LogOperation(string operation)
        {
            using var conn = new SQLiteConnection(ConnectionString);
            conn.Open();
            string logSql = "INSERT INTO Log (Operation, Timestamp) VALUES (@op, @ts)";
            using var cmd = new SQLiteCommand(logSql, conn);
            cmd.Parameters.AddWithValue("@op", operation);
            cmd.Parameters.AddWithValue("@ts", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            cmd.ExecuteNonQuery();
        }

        public static DataTable GetLogs()
        {
            return ExecuteQuery("SELECT * FROM Log ORDER BY Timestamp DESC");
        }
    }
}
